ADO 2.1 DDL and Security Extensions

The beauty of ADO is that it is an extensible architecture. It's not the monolithic (and overly complex) object model that DAO is. Microsoft can easily add new features to ADO without breaking existing applications and without forcing developers to learn a new object model at each new release. In fact, although ADO 2.1 contains several improvements to the ADO 2.0 model, all the new features are provided in the form of distinct object trees that are linked dynamically—that is, at run time—to the object in the standard ADO hierarchy.

In the remainder of this chapter, I'll illustrate the objects in the Microsoft Extension 2.1 for DDL and Security (ADOX) library, which extends the standard ADODB library with data definition language capabilities, giving you, for example, the ability to enumerate the tables, views, and stored procedures in a database as well as the ability to create new ones. This library also contains security objects that permit you to determine and modify the permissions granted to an individual or to groups of users. ADO 2.1 contains other extensions, such as the ADOMD library for online analytical processing (OLAP) operations and the support for Microsoft Jet replications, but I won't cover those topics in this book.

Figure 13-5 depicts the ADOX hierarchy. This object tree includes more items than ADODB, but the relationships among the nodes are still fairly intuitive. Whereas the standard ADO library deals mostly with the data in databases, the ADOX library is interested only in the structure of the tables, the views, and the procedures stored in the database as well as in the users and the groups of users that can access these items. Working with the ADOX library is simple because you don't have to account for recordsets, cursors, timeout errors, locks, transactions, and all the usual issues you have to resolve when writing a standard database application based on ADO. All the objects in the hierarchy also support the Properties collection, which includes all the dynamic properties.

Figure 13-5. The ADOX object model. The Catalog object can be linked to an existing ADODB.Connection object through the Catalog's ActiveConnection property.

The Catalog object is the entry point of the ADOX hierarchy. It represents the database and includes all the tables, stored procedures, views, users, and groups of users. The Catalog object allows you to perform two distinct operations: enumerate the objects in an existing database or create a new database from scratch.

When you just want to explore an existing database, you have to create a stand-alone ADODB.Connection object, open it, and then assign it to the ActiveConnection property of the Catalog object. By doing so, you link the ADODB and the ADOX hierarchies together:

' Edit this constant to match your directory structure. Const DBPATH = "C:\Program Files\Microsoft Visual Studio\Vb98\Biblio.mdb" Dim cn As New ADODB.Connection, cat As New ADOX.Catalog ' Open the connection. cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH ' Link the catalog to the connection. Set cat.ActiveConnection = cn

After you've linked the Catalog object to an open Connection object, you can enumerate the objects in the database by using the Tables, Procedures, Views, Groups, and Users collections:

' Fill a list box with the names of the stored procedures in the database. Dim proc As ADOX.Procedure For Each proc In cat.Procedures List1.AddItem proc.Name Next

On the companion CD, you'll find a complete project that lists all the objects in a Catalog and the values of all their properties, as shown in Figure 13-6.

The Catalog object exposes two methods, GetObjectOwner and SetObjectOwner, which let you read and modify the owner of a database object, as in the following example:

On Error Resume Next ' Not all providers support this capability. owner = cat.GetObjectOwner("Authors", adPermObjTable)

When you create a new (empty) database, you don't need a stand-alone Connection object. Instead, you can carry out the task by using the Catalog object's Create method. This method takes as its only argument the connection string that defines both the provider and the database name:

' The next line fails if the database already exists. cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;" _ & "Data Source=C:\Microsoft Visual Studio\Vb98\BiblioCopy.mdb"

The Create method isn't supported by the OLE DB providers for SQL Server, Oracle, and ODBC drivers.

Regardless of whether you've created a new database or opened an existing one, you can add or remove objects using the Catalogs collections. For example, here's the code that creates a new table with two fields and adds it to the database:

Dim tbl As New ADOX.Table tbl.Name = "Customers" ' Create a table. tbl.Columns.Append "CustID", adInteger ' Add two fields. tbl.Columns.Append "Name", adWVarChar, 50 cat.Tables.Append tbl ' Append the table to ' the collection.

Figure 13-6. This ADOX application shows all the objects in a Catalog and the relationships among them.

The Table object is the most complex object in the ADOX hierarchy. It exposes four simple properties—Name, Type, DateCreated, and DateModified—and the Columns, Indexes, Keys, and Properties collections. These names are fairly self-explanatory, so I won't describe them in depth. The Table object doesn't expose any methods.

All the tables in the database are contained in the Tables collection. This collection exposes the usual Item and Count properties and the Append, Delete, and Refresh methods. For example, you can enumerate all the tables in the database and all the columns in each table by using the following code:

Dim tbl As ADOX.Table, col As ADOX.Column For Each tbl in cat.Tables Print "TABLE " & tbl.Name Print "Created on " & tbl.DateCreated Print "Modified on " & tbl.DateModified Print "Field List ------" For Each col In tbl.Columns Print " " & col.Name Next Next

You can't append or delete tables using the OLE DB providers for Oracle and ODBC drivers.

The Column object and the corresponding Columns collection appear in several places in the ADOX hierarchy, namely, as a dependent object of the Table, Index, and Key objects. The Column object exposes several properties, even though not all of them make sense in all cases. When the Column object is a dependent of a Table object, you can read the Name, Type, DefinedSize, NumericScale, and Precision properties, which have the same meaning as the properties with the same names exposed by the ADODB.Field object. The Column object also supports the Attributes bit-field property, which can be 1-adColFixed or 2-adColNullable.

If the Column object is a dependent of a Key object, you can also set or retrieve the RelatedColumn property, which specifies the name of the related field in the related table. If the Column object is a dependent of an Index object, you can set the SortOrder property with the values 1-adSortAscending or 2-adSortDescending.

You can add a Column object to a table, an index, or a key by using the Append method of the respective Columns collections. This method takes as arguments the name of the column, its type, and (optionally) the value of the DefinedSize property of the Column object to be created:

' Add two fields to the Customers table. Dim tbl As ADOX.Table Set tbl = cat.Tables("Customers") tbl.Columns.Append "CustID", adInteger tbl.Columns.Append "Name", adVarChar, 255

You can enumerate the indexes of a table through its Indexes collection. The Index object exposes a few properties whose names are self-explanatory: Name, Clustered (True if the index is clustered), Unique (True if the index is unique), and PrimaryKey (True if the index is the primary key for the table). The only property that requires a more detailed description is IndexNulls, which specifies whether records with Null values appear in the index. This property can take one of the following values:

Value	Description
0-adIndexNullsAllow	Null values are accepted.
1-adIndexNullsDisallow	The index raises an error if a key column has a Null value.
2-adIndexNullsIgnore	Columns with Null values are ignored and are not added to the index.
4-adIndexNullsIgnoreAny	In a multicolumn index, records are not indexed if any of the index columns has a Null value.

To add an index to a table, you create a stand-alone Index object, set its properties as required, add one or more items to its Columns collection, and finally add the Index to the Indexes collection of a Table object:

Dim tbl As ADOX.Table, ndx As New ADOX.Index ' Create a new index. ndx.Name = "YearBorn_Author" ndx.Unique = True ' Append two columns to it. ndx.Columns.Append "Year Born" ndx.Columns("Year Born").SortOrder = adSortDescending ndx.Columns.Append "Author" ' Add the index to the Authors table. Set tbl = cat.Tables("Authors") tbl.Indexes.Append ndx

You can modify all the properties of an Index object only before the Index is appended to the Indexes collection of a Table object. When you add a field to the Columns collection of an Index object, an error occurs if the column doesn't already exist in the Table object or if the Table object hasn't already been added to the Tables collections of the Catalog object.

The Key object represents a key column in a table. You can enumerate the Keys collection of a Table object to determine its key columns, or you can use the collection's Append method to add new keys. When a key hasn't yet been appended to the collection, you can set its Name and Type properties. The Type property defines the type of the key and can be one of the following values: 1-adKeyPrimary (the primary key), 2-adKeyForeign (a foreign key), or 3-adKeyUnique (a unique key).

If the key is a foreign key, three other properties come into play. The RelatedTable property contains the name of the related table, and the UpdateRule and DeleteRule properties determine what happens to the foreign key if the record in the related table is updated or deleted, respectively. The UpdateRule and DeleteRule properties can contain one of the following values:

Value	Description
0-adRINone	No action is taken.
1-adRICascade	Changes are cascaded.
2-adRISetNull	The key is assigned a Null value.
3-adRISetDefault	The key is assigned its default value.

Each Key object exposes a Columns collection, which contains all the columns that make up the key. The following code shows how you can add a new key to a table:

' Add a foreign key to the Orders table, and make the key point ' to the EmployeeID field of the Employees table. Dim tbl As ADOX.Table, key As New ADOX.Key Set tbl = cat.Tables("Orders") ' Create the key, and set its attributes. key.Name = "Employee" key.Type = adKeyForeign key.RelatedTable = "Employees" key.UpdateRule = adRICascade ' Add a column to the key, and set its RelatedColumn attribute. key.Columns.Append tbl.Columns("EmployeeId") key.Columns("EmployeeId").RelatedColumn = "EmployeeId" ' Append the key to the table's Keys collection. tbl.Keys.Append key

The View object and the Procedure object are similar. They represent a view and a stored procedure in the database, respectively. They also expose the same set of four properties: Name, DateCreated, DateModified, and Command. The Command property provides the maximum flexibility to these objects, without making the ADOX hierarchy more complex than strictly required. In fact, the Command property returns a reference to an ADODB.Command object that can execute the view or the stored procedure, so you can determine the underlying SQL command as well as the name and the type of any parameters using this Command object's properties. The following code example demonstrates how you can extract this information:

Dim cmd As ADODB.Command Set cmd = cat.Views("All Titles").Command MsgBox cmd.CommandText

You also use the auxiliary Command object when you want to create a new view or a stored procedure, as shown in the following code snippet:

' Edit this constant to match your directory structure. Const DBPATH = "C:\Program Files\Microsoft Visual Studio\Vb98\Biblio.mdb" Dim cn As New ADODB.Connection, cmd As New ADODB.Command ' Note the version number of the Jet OLE DB Provider. cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH Set cmd.ActiveConnection = cn cmd.CommandText = "Select * From Authors Where [Year Born] = [Year]" cmd.Parameters.Append cmd.CreateParameter("Year", adInteger, adParamInput) ' Open the Catalog, and create the new procedure. Set cat.ActiveConnection = cn cat.Procedures.Append "AuthorsByYear", cmd

Views aren't supported by the OLE DB Provider for SQL Server. The providers for ODBC and Oracle support them, but you can only enumerate them—you can't add or delete individual View objects. None of these providers can create or delete Procedure objects.

The Catalog object exposes the Groups and Users collections, which include the groups of users and the individual users that can access some or all of the objects in the database. These two objects are tightly connected to each other in that each User object exposes a Groups collection (all the groups the user belongs to), and each Group object exposes a Users collection (all the users that belong to that group).

You can retrieve the permissions assigned to a User object or a Group object using their GetPermissions method. Because this method returns a bit-field value, you must use the AND Boolean operator to understand which operations are allowed:

' Displays which permissions on the Customers table ' have been granted to the users in the Guests group. Dim grp As ADOX.Group, permissions As Long Set grp = cat.Groups("Guests") permissions = grp.GetPermissions("Customers", adPermObjTable) If permissions And adRightExecute Then Print "Execute" If permissions And adRightRead Then Print "Read" If permissions And adRightUpdate Then Print "Update" If permissions And adRightInsert Then Print "Insert" If permissions And adRightDelete Then Print "Delete" If permissions And adRightReference Then Print "Reference" If permissions And adRightCreate Then Print "Create" If permissions And adRightWriteDesign Then Print "Design" If permissions And adRightWithGrant Then Print "Grant Permissions"

The SetPermission method lets you set, grant, deny, or revoke permissions on a given database object to a User or a Group:

' Revoke the Guests group the permission to read the Customers table. cat.Users("Guests").SetPermissions "Customers", adPermObjTable, _ adAccessRevoke, adRightRead ' Give the Managers group full permissions on the Employees table. cat.Users("Managers").SetPermissions "Employees", adPermObjTable, _ adAccessSet, adRightFull

The Groups and the Users collections aren't supported by the OLE DB Provider for SQL Server, Oracle, and ODBC drivers.

In this chapter, I described all the objects in the ADO hierarchy and their many properties, methods, and events. Although I've shown you how to use ADO to carry out some complex tasks, I haven't yet illustrated in practice how you can build complete database applications that exploit ADO's most advanced features. We'll focus on how to use these objects in Chapter 14.